﻿//************************************************************************************************************
//文件名称(File Name)：AccountMana.cs
//功能描述(Description)：用户管理
//数据表(Tables)：
//作者(Author)：
//日期(Create Date)：20130214
//参考文档(Reference)(可选)：
//引用(Using) (可选)﹕
//************************************************************************************************************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DbAdapter.sqldbAdapter;

namespace AnalyseCrmMana
{
    public class AccountMana
    {
        public string username
        {
            get;
            set;
        }

        public string userrelname
        {
            get;
            set;
        }

        public string password
        {
            get;
            set;
        }
        public string email
        {
            get;
            set;
        }
        public string mobile
        {
            get;
            set;
        }
        public string question
        {
            get;
            set;
        }
        public string islock
        {
            get;
            set;
        }
        public string isdelete
        {
            get;
            set;
        }
        public DateTime logdatetime
        {
            get;
            set;
        }

        public string deptment
        {
            get;
            set;
        }

        public string userjob
        {
            get;
            set;
        }
        /// <summary>
        /// 添加新的用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int AddNewUser(AccountMana user)
        {
            string sql = "Insert into Tbt_Users(USERNAME,REALNAME,PASSWORD,EMAIL,MOBILE,QUESTION,ISLOCK,ISDELETE,LOGDATETIME,DEPARTMENT,JOB) "
                + "Values('" + user.username + "','" + user.userrelname + "','" + user.password + "','" + user.email + "','"
                + user.mobile + "','" + user.question + "','"
                + user.islock + "','" + user.isdelete + "','" + user.logdatetime.ToString()+"','"
                + user.deptment + "','" + user.userjob + "')";
            ExecuteSql esql = new ExecuteSql();
            return esql.Execute_Sql(sql);
        }

        /// <summary>
        /// 修改用的EMAIL和手机
        /// </summary>
        /// <param name="username"></param>
        /// <param name="email"></param>
        /// <param name="mobile"></param>
        /// <returns></returns>
        public int UpdateUser(string username,string email,string mobile)
        {
            string sql = "Update Tbt_Users set EMAIL = '" + email + "',MOBILE = '" + mobile 
                       + "' where USERNAME = '" + username + "'";
            ExecuteSql esql = new ExecuteSql();
            return esql.Execute_Sql(sql);
        }

        /// <summary>
        /// 删除用户（逻辑删除）
        /// </summary>
        /// <param name="username"></param>
        /// <param name="isdel"></param>
        /// <returns></returns>
        public int DeleteUser(string username)
        {
            string sql = "Update Tbt_Users set ISDELETE = 'T' where USERNAME = '" + username + "'";
            ExecuteSql esql = new ExecuteSql();
            return esql.Execute_Sql(sql);
        }

        /// <summary>
        /// 解锁或锁定用户
        /// </summary>
        /// <param name="username"></param>
        /// <param name="islock"></param>
        /// <param name="flag">0：解锁（不锁定） 1：锁定</param>
        /// <returns></returns>
        public int OpenAndLockUser(string username, string islock = "T",short flag = 1)
        {
            if (flag == 0)
            {
                islock = "F";
            }
            string sql = "Update Tbt_Users set ISLOCK = '" + islock + "' where USERNAME = '" + username + "'";
            ExecuteSql esql = new ExecuteSql();
            return esql.Execute_Sql(sql);
        }

        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="username"></param>
        /// <param name="pass"></param>
        /// <returns></returns>
        public bool LogOning(string username, string pass)
        {
            string sql = "select count(1) from Tbt_Users where USERNAME = '" + username + "' and PASSWORD = '" + pass + "'";
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(sqlconn.GetConnStr(), CommandType.Text, sql);
            return (Convert.ToInt32(rowcount) > 0) ? true : false;
        }

        public IList<AccountMana> getAccountList(string param)
        {
            string sql = "select USERNAME,REALNAME,(select SELVALUE from Tbt_DownList where SELParent = '4' AND SELID = DEPARTMENT) DEPARTMENT,"
                + "(select SELVALUE from Tbt_DownList where SELParent = '5' AND SELID = JOB) JOB,EMAIL,MOBILE,"
                + "case when ISLOCK = 'T' then '是' else '否' end ISLOCK,"
                + "case when ISDELETE = 'T' then '是' else '否' end ISDELETE,LOGDATETIME from Tbt_Users";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            IList<AccountMana> acclist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                acclist = new List<AccountMana>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    AccountMana accmana = new AccountMana();
                    accmana.username = dr["USERNAME"].ToString();
                    accmana.userrelname = dr["REALNAME"].ToString();
                    accmana.deptment = dr["DEPARTMENT"].ToString();
                    accmana.userjob = dr["JOB"].ToString();
                    accmana.email = dr["EMAIL"].ToString();
                    accmana.mobile = dr["MOBILE"].ToString();
                    accmana.islock = dr["ISLOCK"].ToString();
                    accmana.isdelete = dr["ISDELETE"].ToString();
                    accmana.logdatetime = Convert.ToDateTime(dr["LOGDATETIME"]);
                    accmana.password = "";
                    accmana.question = "";
                    acclist.Add(accmana);
                }
            }
            return acclist;
        }

        public int getAccountListCount()
        {
            string sql = "select count(1) from Tbt_Users"; //where ISDELETE = 'F'
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(sqlconn.GetConnStr(), CommandType.Text, sql);
            return Convert.ToInt32(rowcount);
        }

        /// <summary>
        /// 重置密码
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public int ResetAccountPassword(string username)
        {
            string sql = "update Tbt_Users set PASSWORD = '123456' where USERNAME = '" + username + "'";
            sqldb db = new sqldb();
            object rowcount = db.ExecuteNonQuery(sqlconn.GetConnStr(), CommandType.Text, sql);
            return Convert.ToInt32(rowcount);
        }

    }

    public class SelectBindUsers
    {
        public int ischecked
        {
            get;
            set;
        }
        public string username
        {
            get;
            set;
        }
        public string userrelname
        {
            get;
            set;
        }
        /// <summary>
        /// 根据角色编号获取选择的权限
        /// </summary>
        /// <param name="roleno"></param>
        /// <returns></returns>
        public IList<SelectBindUsers> getAllusersByrole(string roleno)
        {
            string sql = "select CASE WHEN B.USERNAME is null then 0 else 1 end ISCHECKED,"
                       + "A.USERNAME,A.REALNAME from Tbt_Users A LEFT JOIN "
                       + "(SELECT * FROM Tbt_RoleAndUser WHERE ROLENO = '" + roleno + "') B "
                       + "ON A.USERNAME = B.USERNAME";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            IList<SelectBindUsers> userlist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                userlist = new List<SelectBindUsers>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    SelectBindUsers seluser = new SelectBindUsers();
                    seluser.ischecked = Convert.ToInt32(dr["ISCHECKED"]);
                    seluser.username = dr["USERNAME"].ToString();
                    seluser.userrelname = dr["REALNAME"].ToString();
                    userlist.Add(seluser);
                }
            }
            return userlist;
        }
    }
}
